If exists (select 1 from sysobjects where name ='sp_imsgo_seq_gen' and Type='P') then
  Drop PROCEDURE sp_imsgo_seq_gen;
ELSE

ALTER PROCEDURE "DBA"."sp_imsgo_seq_gen"(  in "as_table_name" varchar(50) )

//  ******* START : Statement [Ver:3.0:, DB change date: ] *******
//---------------------@~@~r~n$~$------------------------
///////////////////////////////////////////////////////////////////////////////////
// Copyright © Meditab Software Inc. 2014
//
// Event/Function:
//
// Arguments: table name
//
// Returns: None
//
// Description:
//
// Version History
//     1.0          Dec 29, 2015          New Func          Udit Jindal           Purpose: Getting next tran_id for input table
///////////////////////////////////////////////////////////////////////////////////
BEGIN

    /* --------------Work Flow---------------------------
    * Check if the sequence generator is present or not.
    * If present then use of_getmax_tranid and return tran_id ELSE
    * Check for table name in 'autogenerate_tranid'
    * If present then use of_getmax_tranid and return tran_id ELSE
    * use max(table) + 1 logic
    */

    DECLARE le_counter_1 NUMERIC(10,0);
    DECLARE le_counter_2 NUMERIC(10,0);
    DECLARE le_counter_3 NUMERIC(10,0);
    DECLARE le_dynamic_query VARCHAR(100);
    DECLARE le_tran_id NUMERIC(10,0);

    // Checking sequence generator present
    SELECT COUNT(1) INTO le_counter_1
    FROM syssequence
    WHERE sequence_name ='seq_'+ as_table_name +'_id';

    IF le_counter_1 = 1 THEN
        // present
        SELECT of_getmax_tranid(as_table_name) INTO le_tran_id;
    END IF;

    // not present
    IF le_counter_1 = 0 THEN

        // Checking table present in autogenerate_tranid
        SELECT COUNT(1) INTO le_counter_2
        FROM autogenerate_tranid
        WHERE TABLE_NAME = as_table_name;

        IF le_counter_2 = 1 THEN

            // present
            SELECT of_getmax_tranid(as_table_name) INTO le_tran_id;

            // not present
            ELSE
               // Checking table present
               SELECT COUNT(1) INTO le_counter_3
               FROM systable
               WHERE systable.table_type = 'BASE'
               AND systable.table_name = as_table_name;

               IF le_counter_3 = 1 THEN

                   // present
                   SET le_dynamic_query = 'SELECT MAX(tran_id)+1 INTO  le_tran_id FROM '+ as_table_name;
                   execute(le_dynamic_query);

                   // not present
                   ELSE
                    SELECT -1 INTO le_tran_id;

               END IF;

        END IF;

    END IF;
    COMMIT;
    SELECT le_tran_id AS next_val;

END
End If;


//------------------------------------------------------------END---------------------------------------------------------